The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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]
Solved! Go to Solution.
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]
Ok so how to make the filter command work for my scenario?
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]
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |