need to convert an sql code to dax.

[Anonymous]

Hi Everyone,

 I'm trying to convert an SQL code to DAX. The result of the query is to give a list of distinct count of IDs as a result of a join with another table where the contents of a conditional column is available in another column of the new table. I need to have a count of the distinct resulting list of IDs.

select distinct d.resident_id from fact_resident_daily f
join dim_resident d on f.resident_key = d.resident_key

once i try to include the columns that fulfill the condition i get an error message or the column is greyed out .

I will appreciate  advice/ a solution. Thanks

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Do you create a relationship between the fact table and dim table on resident_key columns in the model in Power BI? If you do, you can create the following measure to get the distinct count. I assume that the relationship is one (dim table) to many (fact table).  

Distinct Count of Id = CALCULATE(DISTINCTCOUNT(dim_resident[resident_id]),RELATEDTABLE(fact_resident_daily))

I have attached a sample file at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.