Reply
yasbos
Resolver II
Resolver II
Partially syndicated - Outbound

Count Distinct Values of column in a Calculated Table Variable in DAX Measure

Hi. I have tables a and b. In my measure, I need to create a calculated table consisting of one column from table a (let's call it "ca") and another column from table b, "cb". now, within the measure, I need to do a distinct count on values from column cb in the calculated table. 

Relationships:

Table b 1 ---- * Table b

Measure I tried:

var vAcctsTbl=calculatetable(  ADDCOLUMNS(values(b[cb]) ,  "AcctKey", max(a[ca]) ) )
return
Now, here is the problem is that dax doesn't allow me to do
DISTINCTCOUNTNOBLANK(vAcctsTbl[AcctKey])  

Is there any way by which I can accomplish this? Please, note that I don't have the option of creating a physical table because I am working on a live connection to SSAS. Thanks.

1 ACCEPTED SOLUTION

Syndicated - Outbound

Ok. I used countx...it worked. Thanks.

View solution in original post

3 REPLIES 3
yasbos
Resolver II
Resolver II

Syndicated - Outbound

To make the question more generic, can I count the distinct values of a specific column in a calculated table variable in a dax measure? Please, note that the data source is analysis services, so the solution cannot involve creating an actual table.

Syndicated - Outbound

Ok. I used countx...it worked. Thanks.

foodd
Super User
Super User

Syndicated - Outbound

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).


https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)