Forum Discussion
Filter table using measure in a multiDimension DirectQuery connection to SAP
Hi All,
I have connected Power BI to SAPHana via direct query using Multidimension mode.(No calculated column, parameters allowed).
only thing I can use right now is measure.
I need to filter out the table before writing any further logic. e,g
Date Location Country Sales Profit
1/1/2022 A C1 500 600
1/2/2022 B C1 700 800
1/3/2022 C C1 1000
1/4/2022 D C1 1100 1200
1/5/2022 B C1 1300 1400
1/6/2022 C C1 1600
1/7/2022 D C1 1800
1/8/2022 A C1 1900 2000
1/9/2022 B C1 2100 2200
1/10/2022 C C1 2300 2400
1/11/2022 D C1 2500 2600
1/12/2022 B C1 2700 2800
Need to filter out locations with zero sales and weekend dates(Excluding Saturday/Sunday)
Output should be something like:
Date Location Country Sales Profit
1/4/2022 D C1 1100 1200
1/5/2022 B C1 1300 1400
1/10/2022 C C1 2300 2400
1/11/2022 D C1 2500 2600
1/12/2022 B C1 2700 2800
Remember its a multidimention connection to Sap
not sure about your setup.
if you can still create a measure and table visuals, try this:
SalesSum = VAR _table = ADDCOLUMNS( TableName, "Weekday", WEEKDAY(TableName[Date], 2) ) VAR _table2 = FILTER( _table, [Weekday]<>6 &&[Weekday]<>7 &&TableName[Profit] <>0 ) RETURN CALCULATE(SUM(TableName[Profit]), _table2)i tried and it worked like this:
5 Replies
hi Anonymous
try to create a table with this:
Table = VAR _table = ADDCOLUMNS( TableName, "Weekday", WEEKDAY(TableName[Date], 2) ) VAR _table2 = FILTER( _table, [Weekday]<>6 &&[Weekday]<>7 &&TableName[Profit] <>0 ) RETURN SELECTCOLUMNS( _table2, "Date", [Date], "Location", [Location], "Country", [Country], "Sales", [Sales], "Profit", [Profit] )i tried and it worked like this:
Good luck.
What is your ultimate goal? If you could have such a table, what would you do next?