Forum Discussion

garythomannCoGC's avatar
garythomannCoGC
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

sql where in dax equivalent

Wondering what change is needed to the query below.  Trying to add a 'where' clause to the working query.

The first commented bit of the filter is what I want.  The other lines are failing tests.

 

Simple dataset table

 

The dax query

 

EVALUATE 
    FILTER ( 
        SELECTCOLUMNS (
             'CPP Traffic Light Short Status',
             "Short Status Name",   'CPP Traffic Light Short Status'[Short Status Name],
             "Unichar Glyph",       UNICHAR ('CPP Traffic Light Short Status'[Unicode Decimal]),
             "Colour Hex",          'CPP Traffic Light Short Status'[Colour Hex], 
             "Font Size",           'CPP Traffic Light Short Status'[Font Size], 
             "Font Size Offset",    'CPP Traffic Light Short Status'[Font Size Offset] 
        ),
--        'CPP Traffic Light Short Status'[Status Code] IN {0, 1, 2, 3, 10}
--        MAX('CPP Traffic Light Short Status'[Status Code]) = 0
          MAX('CPP Traffic Light Short Status'[Short Status Name]) = "Not Reported"
    )
ORDER BY [Short Status Name]

 

 

  • garythomannCoGC's avatar
    garythomannCoGC
    4 years ago

    Replace filter with calculatetable command :}

     

    EVALUATE 
--    FILTER ( 
    CALCULATETABLE ( 
        SELECTCOLUMNS (
             'CPP Traffic Light Short Status',
             "Short Status Name",   'CPP Traffic Light Short Status'[Short Status Name],
             "Unichar Glyph",       UNICHAR ('CPP Traffic Light Short Status'[Unicode Decimal]),
             "Colour Hex",          'CPP Traffic Light Short Status'[Colour Hex], 
             "Font Size",           'CPP Traffic Light Short Status'[Font Size], 
             "Font Size Offset",    'CPP Traffic Light Short Status'[Font Size Offset] 
        ),
        'CPP Traffic Light Short Status'[Status Code] IN {0, 1, 2, 3, 10}
--        'CPP Traffic Light Short Status'[Status Code] = 0
--          'CPP Traffic Light Short Status'[Short Status Name] = "Not Reported"
    )
ORDER BY [Short Status Name]

2 Replies

  • garythomannCoGC's avatar
    garythomannCoGC
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Replace filter with calculatetable command :}

     

    EVALUATE 
--    FILTER ( 
    CALCULATETABLE ( 
        SELECTCOLUMNS (
             'CPP Traffic Light Short Status',
             "Short Status Name",   'CPP Traffic Light Short Status'[Short Status Name],
             "Unichar Glyph",       UNICHAR ('CPP Traffic Light Short Status'[Unicode Decimal]),
             "Colour Hex",          'CPP Traffic Light Short Status'[Colour Hex], 
             "Font Size",           'CPP Traffic Light Short Status'[Font Size], 
             "Font Size Offset",    'CPP Traffic Light Short Status'[Font Size Offset] 
        ),
        'CPP Traffic Light Short Status'[Status Code] IN {0, 1, 2, 3, 10}
--        'CPP Traffic Light Short Status'[Status Code] = 0
--          'CPP Traffic Light Short Status'[Short Status Name] = "Not Reported"
    )
ORDER BY [Short Status Name]