topic Re: using filter and divide in a meaure in DAX Commands and Tips

using filter and divide in a meaure

Shega — Tue, 21 Feb 2023 16:37:16 GMT

Hello,

just an info, I am new to PBI from a DBA background and working on converting a report that is written on OBIEE. Here is one problem I couldn't solve:

fact_tbl.gl * 100/filter(fact_tbl.rc USING (fact_tbl.cust='X'))

fact_tbl.gl is float datatype

fact_tbl.rc is float datatype

fact_tbl.cust is char datatype

I used the PBI filter but the USING clause part is an issue that I couldn't come up with a good solution. When I tried as below, it is not giving me the correct result.

Custx = divide(calculate(fact_tbl.gl]*100),calculate(fact_tbl[rc]),filter(fact_tbl, fact_tbl[cust] ="X"))

Re: using filter and divide in a meaure

mark_endicott — Mon, 24 Jun 2024 15:43:01 GMT

Shega - The DAX below should work for you, with the exception of having to change the table/column names:

VAR gl = SUMX(fact_tbl, fact_tbl[gl] * 100) VAR rc = CALCULATE( SUM(fact_tbl[rc]), KEEPFILTERS( fact_tbl[cust] ="X")) RETURN DIVIDE( gl, rc, 0 )

If this works, please accept it as the solution.

Re: using filter and divide in a meaure

mark_endicott — Thu, 27 Jun 2024 09:48:39 GMT

Shega - Did this work? Please could you mark it as the solution if yes, this helps other users find it.