Forum Discussion
Complex DAX Statement based on existence in Dim; FTE based so sometimes sum and sometimes don't
As I work through a Power BI model, I’ve encountered a challenging DAX scenario that I’d love to get input on. The model consists of a fact table with straightforward relationships to dimensions such...
Hi MikeHendriks ,
As far as I understand, your requirement is to use different calculation on different levels in your matrix or visual table.
If my diagnosis is correct, you should consider using functions like (HASONEFILTER, ISFILTERED, ISCROSSFILTERED, ISINSCOPE...) to identify at which level of your table you are.
In you case it means that you will test if a COSTCENTER, a FLOOR etc has a filter or not, or a single value (with HASONEVALUE). According to that yuo can adjust your calculation.
For instance in the following formula I'm testing if I'm on the level of an invoice, if yes then I want a BLANK, otherwise it means that I'm at the client level in my visual so I want the count of invoice.
Number of invoices (with test) =
IF( HASONEFILTER( SALES[ID_Invoice] ) //If one invoice is filtered
, BLANK() , //Return a blank instead of 1
[Measure count of invoice] ) //Return the measure that counts the invoices
or another example :
Total Sales OR Average invoice amount of client = //Dispaly the sum or the average
IF(
HASONEVALUE( SALES[ID_Invoice] ) , //If there is only one invoice, we want the total sales of the invoice
FORMAT( [Total Sales] ,"#,### EUR" ) , //So we display and format the measure Total Sales (a simple SUMX( SALES, Price * Qty ) )
IF( NOT ISBLANK([Average amount of invoices of client]) , "Average:" & FORMAT( [Average amount of invoices of client] ,"#,### EUR" , BLANK() ) )
)//If you have more than one invoice (you are on the client level in the table) so display Average amount, except if its blank
Hope it helps