Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all.
I'm really struggling with some DAX and would appreciate some help.
Requirement
I want a count of how many 'Apps' are not equal to 'Complete' for a specific 'Build'.
Data Model
Builds Build App Apps App (FK into Builds.App) Status
I previously asked a question here: DAX subquery measure?
for instruction on how to create a specific measure column for a visualisation. To keep the example simple, I kept it to one fictitious table and the DAX query worked really well.
In reality, however, the visualisation that the measure column is for is made up of multiple joined tables. And the results of the DAX query unexpectedly produced all zeros! So I'll refactor my example here for more help...
Requirement
I want a count of how many 'Apps' are not equal to 'Complete' for a specific 'Build'.
Data Model
Builds Build App Apps App Status
Sample Data
Builds
Build...........App Build1..........App1 Build1..........App2 Build1..........App9 Build2..........App3 Build3..........App1 Build3..........App5 Build3..........App8 Build3..........App9
Apps
App...........Status App1..........UAT App2..........Complete App9..........New App3..........Complete App5..........UAT App8..........Complete
Relationship
The relationship is MANY Builds.App to ONE Apps.App.
Visualisation Table
This is my visualisation - note the different tables:
Builds.Build....Builds.App....Apps.Status Build1..........App1..........UAT Build1..........App2..........Complete Build1..........App9..........New Build2..........App3..........Complete Build3..........App5..........UAT Build3..........App8..........Complete
This is my required results (for example, i can see there are 2 Apps not Complete for Build1):
Builds.Build....Builds.App....Apps.Status....AppsNotCompleteForBuild Build1..........App1..........UAT............2 Build1..........App2..........Complete.......2 Build1..........App9..........New............2 Build2..........App3..........Complete.......0 Build3..........App5..........UAT............1 Build3..........App8..........Complete.......1
ATTEMPT 1 (Not working!)
CALCULATE ( COUNT ( Builds[App] ), FILTER ( ALL ( Builds[Build], Builds[App] ), Builds[Build] = SELECTEDVALUE ( Builds[Build] ) ), FILTER ( ALL ( Apps[Status] ), Apps[Status] <> "Complete" ) ) + 0
ATTEMPT 2 (Not working!)
Measure 5 = CALCULATE ( COUNT ( Builds[App] ), FILTER ( ALL ( Builds[Build] ), Builds[Build] = SELECTEDVALUE ( Builds[Build] ) ), FILTER (RELATEDTABLE(Apps), Apps[Status] <> "Complete") ) + 0
ATTEMPT 3 (Not working!)
Measure5 = CALCULATE ( COUNTAX(FILTER( Builds , RELATED(Apps[Status]) <>"Complete" && Builds[Build] = SELECTEDVALUE(Builds[Build]) ) ,Builds[App]) ) + 0
Thanks for any help!!
AppsNotCompleteForBuild =
IF (
NOT ISEMPTY ( Builds ),
CALCULATE (
DISTINCTCOUNT ( Apps[App] ),
Apps[Status] <> "Complete",
CALCULATETABLE ( Builds, ALLEXCEPT ( Builds, Builds[Build] ) )
) + 0
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hey @captain_planet ,
this measure
cnt incomplete apps by build =
var filtertable =
ADDCOLUMNS(
SUMMARIZE(
'Builds'
, Builds[Build]
, Builds[App]
, Apps[Status]
)
, "no of apps"
, CALCULATE(
COUNT( Builds[App] )
, ALL( Builds[App] )
, 'Apps'[Status] <> "Complete"
)
)
return
IF( HASONEVALUE( 'Builds'[Build] )
,SUMX(
filtertable
, IF(
ISBLANK( [no of apps] )
, 0
, [no of apps]
)
)
, BLANK()
)
allows to create this table visual:
Hopefully, this is what you are looking for.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |