The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |