Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
My data:
PersonId | ServiceName | Year | WeekNr | Weekday | Pause |
1 | Paper | 2021 | 50 | monday | False |
1 | Paper | 2021 | 50 | tuesday | False |
1 | Wood | 2021 | 51 | monday | True |
1 | Wood | 2021 | 51 | tuesday | True |
2 | Paper | 2022 | 4 | monday | False |
3 | Tomato | 2022 | 2 | monday | True |
3 | Tomato | 2022 | 2 | tuesday | False |
3 | Tomato | 2022 | 3 | monday | False |
3 | Tomato | 2022 | 3 | tuesday | False |
So - I need to find two things in order to get the result I need.
If A = 2 and B = True then the person gets billed (Billed = 1 else 0)
Meaning, for the above table:
PersonId | ServiceHame | Year | WeekNr | Billed (measure) |
1 | Paper | 2021 | 50 | 1 |
1 | Wood | 2021 | 51 | 0 (2x True) |
2 | Paper | 2022 | 4 | 0 (only 1 row) |
3 | Tomato | 2022 | 2 | 1 |
3 | Tomato | 2022 | 3 | 1 |
I'd love this to work in a measure, since I'm working off a PBI Dataset.
Can anyone lead me in the right direction?
Solved! Go to Solution.
@grggmrtn
Can you try this measure please:
Billed =
VAR __PAUSE = VALUES( Table2[Pause] )
RETURN
IF (
COUNTROWS(Table2) = 2 && FALSE() IN __PAUSE ,
1,
0
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@grggmrtn
Can you try this measure please:
Billed =
VAR __PAUSE = VALUES( Table2[Pause] )
RETURN
IF (
COUNTROWS(Table2) = 2 && FALSE() IN __PAUSE ,
1,
0
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I'm seriously impressed! That was a LOT easier than I had imagined, and I'm still not sure why it's working, but it is 😉
Is there any way to do this with a SUMMARIZE instead of an actual "physical" table? My data, in reality, is comprised of a factless fact table and a bunch of dimensions...
@grggmrtn
Glad it solved your problem!
How it works ?
VALUES captures the currently visible values from the Pause column and stores in __PAUSE virtual table, the IF condition checks if there are two rows in the table, and using the IN operator it also checks if FALSE() value exists within __PAUSE table, then returns 1 else 0
Hope I it was clear or confused you 🙂
Regarding your 2nd question, yes, you can perform this using a virtual table as well, it depends on the type of model and tables involved.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Yeah I'm having big problems getting it virtual - The measure that I actually got to WORK (but give wrong results was
Afregning =
VAR Pause = VALUES(FACT_Indsatser[PauseFlag])
VAR Tab = COUNTROWS(
CALCULATETABLE(
SUMMARIZE(
FACT,
Person[PersonId],
Dato[YearWeek],
Dato[WeekdayName],
Department[DepartmentName],
Service[SerciceName],
FACT[Pause]),
Dato[Ugedagnavn] = "Mandag" || Dato[Ugedagnavn] = "Tirsdag" && FALSE() IN Pause
)
)
RETURN
IF(Tab = 2, 1, 0)
(The CALCULATETABLE part is what worked for me, and gave me the correct values earlier, when I used it to create an actual "physical" table)
It seems a lot of the problem COULD be that the calculated table isn't getting filtered with the filters that I've put on the actual dimensions.
It could also be that I have no idea what I'm doing when it comes to virtual tables...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
95 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
109 | |
98 | |
93 |