Forum Discussion
need to convert an sql code to dax.
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
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.
1 Reply
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.