Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
106 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
158 | |
124 | |
75 | |
74 | |
63 |