The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
this is my measure:
Measure = CALCULATE( COUNTA('Services'[Aktive]); 'Services'[Aktive]=TRUE(); 'Pause'[Pausestatus]=TRUE() )
and it works as needed. BUT I need to make sure that the values (text) in 'Services'[Provider] and 'Pause'[Provider] are identical as well.
Basically if I COULD, I would just have 'Services'[Provider]='Pause'[Provider] as a filter in the CALCULATE function, but I'm drawing a blank as to how I can do that.
Anyone?
Solved! Go to Solution.
Just a sidenote:
I already found the solution using M - but this created VERY heavy queries that take way too much time to load. But let me try and explain what I did - it MIGHT help with the current problem trying to figure out how to DAX it:
I started by merging 'Services' and 'Pause'
#"Merged queries" = Table.NestedJoin(#"Renamed columns", { "PersonID", "Service", "Provider", "DaysList" }, #"Pause", { "PersonID", "Service", "Provider", "Date" }, "Pause", JoinKind.LeftOuter ), #"Expanded pause" = Table.ExpandTableColumn(#"Merged queries", "Pause", { "Pause" }, { "Pause" } ),
I also created date columns with the year, week number, and name of the day.
Then afterwards I created two index columns - one from 1 and the other from 0. I merged them together which created new "Previous" columns (PersonID, Service, Provider, Pausestatus) (shifted one place from the originals) and then created a new calculated column that does what I'm now trying to do in DAX:
#"Added custom" = Table.AddColumn(#"Expanded previous", "Billing", each if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Year] = [Previous.Year] and [Week number] = [Previous.Week number] and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Pause] = 0 and [Previous.Pause] = 0 then 1 else 0, Int64.Type ),
I'm not sure if this all makes sense, but it was worth a try 😉
Hi @grggmrtn
Not sure if you’d like to get the result like this:
You can use the measure below and filter the measure is not blank:
Measure = var a = CALCULATE(MAX(Services[ID]),FILTER(Services,Services[Aktive]=TRUE())) var b = CALCULATE(MAX(Pause[ID]),FILTER(Pause,Pause[Pausestatus]=TRUE())) Return IF(a=b&&NOT(ISBLANK(a)),CALCULATE( COUNTA(Services[Aktive]) ))
if not, please share us more details about your data. And make sure the confidential info have been masked.
Best regards,
Dina Ye
Hey @v-diye-msft thanks for the reply! What you've posted seems to be exacly what I need.
I wasn't sure if by "Services[ID]" you were referring to [Provider], so that's what I put into the measure. Just to be clear, [Provider] does not contain unique values, such as in an ID column.
It seems to work fine - my weekly results are working, but I'm not sure why the totals aren't always counting correctly in the matrix visualisation:
As far as I know, 8x1 shouldn't be 9 XD - but maybe that's because [Provider] does not contain unique values?
There are more results like this farther down the visualisation, unfortunately.
Hi @grggmrtn ,
Would you mind sharing me your detailed data? sorry I can barely replicate your issue. and the new capture you attached seems doesn't contain any columns you indicated in the previous post, which make me a little bit confused.
Dina
Hi @v-diye-msft sorry for the confusion!
I've created a dummy .pbix and uploaded to dropbox here. Sorry about dropbox, our coorporate OneDrive prevents us from sharing outside of our company.
I hope the data is a bit more understandable, but feel free to ask if you need clarification. And thank you so much for your help!
Just a sidenote:
I already found the solution using M - but this created VERY heavy queries that take way too much time to load. But let me try and explain what I did - it MIGHT help with the current problem trying to figure out how to DAX it:
I started by merging 'Services' and 'Pause'
#"Merged queries" = Table.NestedJoin(#"Renamed columns", { "PersonID", "Service", "Provider", "DaysList" }, #"Pause", { "PersonID", "Service", "Provider", "Date" }, "Pause", JoinKind.LeftOuter ), #"Expanded pause" = Table.ExpandTableColumn(#"Merged queries", "Pause", { "Pause" }, { "Pause" } ),
I also created date columns with the year, week number, and name of the day.
Then afterwards I created two index columns - one from 1 and the other from 0. I merged them together which created new "Previous" columns (PersonID, Service, Provider, Pausestatus) (shifted one place from the originals) and then created a new calculated column that does what I'm now trying to do in DAX:
#"Added custom" = Table.AddColumn(#"Expanded previous", "Billing", each if [PersonID] = [Previous.PersonID] and [Service] = [Previous.Service] and [Provider] = [Previous.Provider] and [Year] = [Previous.Year] and [Week number] = [Previous.Week number] and [Day] = "Tuesday" and [Previous.Day] = "Monday" and [Pause] = 0 and [Previous.Pause] = 0 then 1 else 0, Int64.Type ),
I'm not sure if this all makes sense, but it was worth a try 😉
Hi @grggmrtn ,
Sorry for my late reply. Great to know you've fixed it!
Best regards,
Dina Ye
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
138 | |
106 | |
101 | |
74 | |
59 |
User | Count |
---|---|
259 | |
124 | |
114 | |
98 | |
87 |