Convert query to dax within table loop

Hi danny3313 ,

If you can aggregate data before bringing into Data model and reduce record count, that is one way. But if you really need 500Million records in Power BI Datamodel then try to do it in Power Query. If that too fails then your existing Database is the only option.

Sorry I couldn't help.

hi danny3313 ,

If you are fine with Power Query. Try this.

1. Add a custom column to number each row 1 if its BOTTLE otherwise 0.

2. Group rows by Receipt Number and MAX on the aboved added column, also add AllRows.

3. Expand all rows.

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "HasBottle", each if [ITEM] = "WATER BOTTLE 1L" then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"RECEIPT_NO"}, {{"MAX", each List.Max([HasBottle]), type number}, {"ALLROWS", each _, type table [ITEM=nullable text, RECEIPT_NO=nullable number, HasBottle=number]}}),
#"Expanded ALLROWS" = Table.ExpandTableColumn(#"Grouped Rows", "ALLROWS", {"ITEM"}, {"ITEM"})

In Power BI create a measure 

ItemCount = CALCULATE( COUNT(TestTbl4[ITEM]), TestTbl4[HASBOTTLE] = 1, KEEPFILTERS(TestTbl4[ITEM] <> "WATER BOTTLE 1L" ))

 

 

Then Apply TOPN filter on visual from filter pane.

See if it works.