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,
I have a question that is more related to the Tabular model and Excel connected to that model then Power BI but it's still viable in Power BI. My model is 3 tables: Date creation, Date resolution and issues (below)
ID | Created Date | Resolution Date| Resolution
1 |2019-09-01 | 2019-09-02 | Done
2 |2019-09-01 | |
3 |2019-09-01 | 2019-09-02 | Won't Fix
4 |2019-09-01 | |
The active relation is Date creation > Issue and Date resolution > Issue. There are two measures in Issue table
Issue created count:= COUNTROWS ( Issue )
Issue resolved count:= CALCULATE ( COUNTROWS ( Issue ); FILTER ( Issue; Issue[Resolution] <> BLANK () ) )
And now for the main question: Is there any way of showing values or blank depending on which calendar is chosen on rows/columns? What I need is when the user connects from Excel and chose Date resolution, the "Issue resolved count" should show values but "Issue created count" should show blank. Also when the user chose the Date creation, the "Issue resolved count" should show blank but "Issue created count" should show values. When no calendar is selected then both values should be shown.
-- DAX rule number 1: NEVER filter a table -- when you can filter a column. -- measure 1 (internal, hidden) [_IssuesCreated] = COUNTROWS ( Issue ) -- measure 2 (internal, hidden) [_IssuesResolved] = calculate( [_IssuesCreated], keepfilters ( not isblank( Issue[Resolution] ) ) ) -- measure 3 [Calendar Chosen] = var __creation = isfiltered( 'Creation Dates' ) var __resolution = isfiltered( 'Resolution Dates' ) var __both = __creation && __resolution return switch( true(), __both, "both", __creation, "creation", __resolution, "resolution", "none" ) -- measure 4 [Issues Resolved] = var __calendarChosen = [Calendar Chosen] var __value = [_IssuesResolved] return switch( true(), __calendarChosen in {"both", "creation"}, blank(), __calendarChosen in {"resolution", "none"}, __value, blank() // just in case but should not happen ) -- measure 5 [Issues Created] = var __calendarChosen = [Calendar Chosen] var __value = [_IssuesCreated] return switch( true(), __calendarChosen in {"both", "creation"}, __value, __calendarChosen in {"resolution", "none"}, blank(), blank() // just in case but should not happen )
Best
D.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |