DistinctCount with condition in Power Query

[pmagadum]

Hi

I have a measure created in DAX 

Measure1 = CALCULATE(DISTINCTCOUNT(Fact_Details[Column3]),Fact_Details[Column2]="Core",Fact_Details[Column3]<>BLANK())

Im trying the same thing in Power Query

= Table.Group(Source, {"Column1", "Column2"}, {{"Measure1", each List.Count(List.Distinct(_[Column3])), Int64.Type} })

Is this correct? How do i calcualte distinctcount of a coumn with condition?

Thanks,

Pradeep

[AlexisOlson]

The distinct count part looks fine.

You can add those extra conditions as a filter before doing the Group By.

[pmagadum]

What if ?

= Table.Group(Source, {"Column1", "Column2"}, {{"Measure1", each List.Count(List.Distinct(_[Column3])), Int64.Type}, {"Measure2", each List.Count(List.Distinct(_[Column4])), Int64.Type} })

Scenario:

If Column2="Core" then distinct count of Column3

If Column2="Non Core" then distinct count of Column4

In this case i cannot filter the rows before doing the group by. 

[Anonymous · Accepted solution]

Hi @pmagadum ,

Try this code

= Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(Table.Distinct(Table.SelectRows(_, each [Column3]<>""))), Int64.Type}})

Before

After

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.