Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need help with a DAX measure. I want to perform a COUNT. This COUNT should be filtered by DATESINPERIOD, which is 2022. The challenge is, that the date actually can be in 1 of 2 columns. Lets say Schritt10 or Schritt11 for example. How can i write the dax measure?
I tried some measures but they always end in errors : )
For example:
try no. 1: CountIn2022 = COUNTROWS( FILTER( YourTable, (YEAR(YourTable[Schritt10 ]) = 2022 || YEAR(YourTable[Schritt11]) = 2022)
try no. 2:
CountIn2022 =
VAR Schritt10 =
DATESINPERIOD (
YourTable[Schritt10Abgeschlossen],
DATE ( 2022, 01, 01 ),
1,
YEAR
)
VAR Schritt11 =
DATESINPERIOD (
YourTable[Schritt11Abgeschlossen],
DATE ( 2022, 01, 01 ),
1,
YEAR
)
RETURN
CALCULATE (
COUNT ( YourTable[Id] ),
(
Schritt10=TRUE() || Schritt11=TRUE()
))
I hope someone can help me.
Thank you in advance.
Solved! Go to Solution.
You could consider unpivoting your data to simplify things. This approach won't be performant at large scale, but here is one way to do it.
DIP =
VAR dip =
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
VAR result =
COUNTROWS (
FILTER ( YourTable, YourTable[Schritt10] IN dip || YourTable[Schritt11] IN dip )
)
RETURN
result
If you have many duplicate rows in YourTable, this should be faster.
DIP2 =
VAR dip =
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
VAR summarizedates =
SUMMARIZE (
YourTable,
YourTable[Schritt10],
YourTable[Schritt11],
"cCount", COUNT ( YourTable[Schritt10] )
)
VAR result =
SUMX (
FILTER (
summarizedates,
YourTable[Schritt10]
IN dip
|| YourTable[Schritt11] IN dip
),
[cCount]
)
RETURN
result
Pat
You could consider unpivoting your data to simplify things. This approach won't be performant at large scale, but here is one way to do it.
DIP =
VAR dip =
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
VAR result =
COUNTROWS (
FILTER ( YourTable, YourTable[Schritt10] IN dip || YourTable[Schritt11] IN dip )
)
RETURN
result
If you have many duplicate rows in YourTable, this should be faster.
DIP2 =
VAR dip =
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
VAR summarizedates =
SUMMARIZE (
YourTable,
YourTable[Schritt10],
YourTable[Schritt11],
"cCount", COUNT ( YourTable[Schritt10] )
)
VAR result =
SUMX (
FILTER (
summarizedates,
YourTable[Schritt10]
IN dip
|| YourTable[Schritt11] IN dip
),
[cCount]
)
RETURN
result
Pat
Thank you ppm1,
the first approach works perfectly!
I didnt know about the IN.
User | Count |
---|---|
51 | |
38 | |
20 | |
14 | |
13 |
User | Count |
---|---|
98 | |
71 | |
29 | |
20 | |
13 |