Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |