Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
[Referring to the screenshot] About the data: I've prepared the model where in the 'Date' as seen in the screenshot is an intermediate table and 'Code' is also an intermediate table and the Measures 'Visits Total' & 'IA visits','Vacation Visits' are from different tables. These tables primary dates and codes columns are connected to the intermediate table.
Requirement: if (any of the measure has no data/0 then i want the result as 'Count the respective date as 1 day' ) the result i wanted is highlighted in the RED color in screenshot.
Hoping for the DAX help on this.Would be grateful for the help in this regard. Thanks.
Solved! Go to Solution.
Hi @PuneethERB ,
You could create two measures.
Measure =
VAR a =
PRODUCTX (
FILTER (
'Table',
'Table'[Value] IN { BLANK (), 0 }
&& (
CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 0
)
),
1
)
RETURN
IF (
NOT ( ISINSCOPE ( 'Table'[Type] ) ) && ISINSCOPE ( 'Table'[Date] )
&& SELECTEDVALUE ( 'Table'[Value] ) IN { BLANK (), 0 },
a,
SUM ( 'Table'[Value] )
)
Measure new =
SUMX(VALUES('Table'[Date]),[Measure])
Then put "Measure new" into Values.
Here is the result.
Here is my test file for your reference.
Hi @PuneethERB ,
You could create two measures.
Measure =
VAR a =
PRODUCTX (
FILTER (
'Table',
'Table'[Value] IN { BLANK (), 0 }
&& (
CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ) = 0
)
),
1
)
RETURN
IF (
NOT ( ISINSCOPE ( 'Table'[Type] ) ) && ISINSCOPE ( 'Table'[Date] )
&& SELECTEDVALUE ( 'Table'[Value] ) IN { BLANK (), 0 },
a,
SUM ( 'Table'[Value] )
)
Measure new =
SUMX(VALUES('Table'[Date]),[Measure])
Then put "Measure new" into Values.
Here is the result.
Here is my test file for your reference.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |