Need to check if values in 2 columns of 2 different tables are equal before applying measure

[grggmrtn]

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?

[v-diye-msft]

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

[grggmrtn]

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.

[v-diye-msft]

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

[grggmrtn]

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!

[grggmrtn · Accepted 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 😉

[v-diye-msft]

Hi @grggmrtn ,

Sorry for my late reply. Great to know you've fixed it!

Best regards,

Dina Ye