Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Dax measure / need help with a measure

Hi everyone,

I would like to do this measure : 

calculate the number of interventions using the distinctcount function for which there is neither the "A61" nor the "A62" intervention, but also does not take into account other interventions including the co-response codes A61 AND A62 Here is the table. Table name: Table. 

Another information: there are a lot of data like this. 

 

65+.PNG

Thank you, My best regards. 

2 ACCEPTED SOLUTIONS
d_gosbell
Super User
Super User

I'm sorry, but I don't understand your question.

 

What do you mean by "calculate the number of interventions"?

 

Can you tell us what result you would expect the measure to return from the sample data you posted?

View solution in original post


@Anonymous wrote:

the result will be = 3


Ah ok, So you are counting distinct values from the Codes column provided that none of the interventions for that code are A61 or A62.

 

One was of doing this is a measure like the following:

DCount Codes = 
var _excludedCol1 = CALCULATETABLE(ALLSELECTED('Table'[Codes]), FILTER(ALL('Table'),'Table'[Intervention] IN {"A61","A62"}))
var _result = CALCULATE(DISTINCTCOUNT('Table'[Codes]), not 'Table'[Codes] in _excludedCol1)
return _result 

I basically create the first variable as a list of Codes that do have one of A61 or A62, then I exclude these codes from the distinct count.

View solution in original post

4 REPLIES 4
d_gosbell
Super User
Super User

I'm sorry, but I don't understand your question.

 

What do you mean by "calculate the number of interventions"?

 

Can you tell us what result you would expect the measure to return from the sample data you posted?

Anonymous
Not applicable

Hi, 

 

to be more precise 231.PNG

 

Anonymous
Not applicable

the result will be = 3


@Anonymous wrote:

the result will be = 3


Ah ok, So you are counting distinct values from the Codes column provided that none of the interventions for that code are A61 or A62.

 

One was of doing this is a measure like the following:

DCount Codes = 
var _excludedCol1 = CALCULATETABLE(ALLSELECTED('Table'[Codes]), FILTER(ALL('Table'),'Table'[Intervention] IN {"A61","A62"}))
var _result = CALCULATE(DISTINCTCOUNT('Table'[Codes]), not 'Table'[Codes] in _excludedCol1)
return _result 

I basically create the first variable as a list of Codes that do have one of A61 or A62, then I exclude these codes from the distinct count.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.