Filter table using measure in a multiDimension DirectQuery connection to SAP

[Anonymous]

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

@tamerj1   @FreemanZ  @amitchandak 

[FreemanZ]

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.

[Anonymous]

Thank you so much for providing the DAX. Unfortunately New table option is greyed out in direct query multidimension connection.

[FreemanZ]

What is your ultimate goal? If you could have such a table, what would you do next?

[Anonymous]

My ultimate goal is to calculate the sales after filtering out both the conditions in live connection. 

Since its is Live so I cannot create a table.

[FreemanZ · Accepted solution]

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: