PowerBI DAX error: comparing values of type text with values of type integer

user_16501869 0
Viewed 66

I need help with a PowerBI error. I cannot display any data because of this error I've been receiving from DAX: dax comparison operations do not support comparing values of type text with values of type integer.

It appears that the error is in this part of the formula:

    FILTER('tgv vDimWorkspaceArea', 'tgv vDimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey && 'tgv vDimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey),
    FILTER('tgv vDimDeviceWorkspaceAreaParty', 'tgv vDimDeviceWorkspaceAreaParty'[ValidFromKey] <= MaxSelectedDateKey && 'tgv vDimDeviceWorkspaceAreaParty'[ValidToKey] >= MinSelectedDateKey)

I have checked the formats of the "ValidToKey" and "ValidFromKey", they are both in the format of Whole number in the table (e.g. 20220421). Any ideas where else I should look?

Thank you

dax powerbi comparison
2 Answers

no it's not possible to get blank. The ValidTo and ValidFrom keys are in the format of date. I tried the rest of the formula via trial and error and it was working. When I inserted these filter queries it stopped working.

Here is the rest of the formula:

Index = 
VAR MinSelectedDateKey = MIN('Calendar'[DateKey])
VAR MaxSelectedDateKey = MAX('Calendar'[DateKey])
RETURN
(
    CALCULATE(
        COUNTROWS('dm FactEnviroData_Agr60_new'),
        FILTER(
            'dm FactEnviroData_Agr60_new',
            'dm FactEnviroData_Agr60_new'[ComfortCO2] = "High" && 'dm FactEnviroData_Agr60_new'[ComfortHumidity] = "High" 
            || 'dm FactEnviroData_Agr60_new'[ComfortCO2] = "High" && 'dm FactEnviroData_Agr60_new'[ComfortHumidity] = "Medium" 
        ),
        FILTER('dm DimWorkspaceArea', 'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey && 'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey),
        FILTER('dm DimDeviceWorkspaceAreaParty', 'dm DimDeviceWorkspaceAreaParty'[ValidFromKey] <= MaxSelectedDateKey && 'dm DimDeviceWorkspaceAreaParty'[ValidToKey] >= MinSelectedDateKey)
    ) + 0
)  /  
CALCULATE(
    COUNTROWS('dm FactEnviroData_Agr60_new'),
    'dm FactEnviroData_Agr60_new'[ComfortCO2] <> "null",
    FILTER('dm DimWorkspaceArea', 'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey && 'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey),
    FILTER('dm DimDeviceWorkspaceAreaParty', 'dm DimDeviceWorkspaceAreaParty'[ValidFromKey] <= MaxSelectedDateKey && 'dm DimDeviceWorkspaceAreaParty'[ValidToKey] >= MinSelectedDateKey)
)
*100
user_165018690

Check steps:

1.

CALCULATE(
    COUNTROWS('dm FactEnviroData_Agr60_new'),
    'dm FactEnviroData_Agr60_new'[ComfortCO2] <> "null"
    ,FILTER(
        'dm DimWorkspaceArea'
        ,'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey 
        && 'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey
    )
    ,FILTER(
        'dm DimDeviceWorkspaceAreaParty'
        ,'dm DimDeviceWorkspaceAreaParty'[ValidFromKey] <= MaxSelectedDateKey 
        && 'dm DimDeviceWorkspaceAreaParty'[ValidToKey] >= MinSelectedDateKey)
)

CALCULATE(
    COUNTROWS('dm FactEnviroData_Agr60_new'),
    'dm FactEnviroData_Agr60_new'[ComfortCO2] <> "null"
)

 COUNTROWS(
      FILTER(
         'dm DimWorkspaceArea'
         ,'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey 
          && 'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey
      )
 )

  COUNTROWS(
       FILTER(
          'dm DimWorkspaceArea'
          ,-- 'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey 
           --&& 
          'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey
       )
  )

 COUNTROWS(
      FILTER(
         'dm DimWorkspaceArea'
         ,'dm DimWorkspaceArea'[ValidFromKey] <= MaxSelectedDateKey 
          -- && 'dm DimWorkspaceArea'[ValidToKey] >= MinSelectedDateKey
      )
 )
user_190423790
Related