Excel to DAX assistance

[AlAlawiAlawi]

Hello Gentlemen;

I would truly appreciate some assitance to this delimma of mine.

PHASE 1 - Convert the below logic to DAX for a New Column called Lost.Sale

=IF(OR([@[Lead Status]]="X",[@[Lead Status]]="7-LOST SALE"),
IF(AND(COUNTIFS([Sold T],"="&[@[Customer ID]],
[New/Used],"="&[@[New/Used]],
[Brand],"="&[@Brand],[Year],"="&[@Year])<1,
COUNTIFS([Open],"="&[@[Customer ID]],
[New/Used],"="&[@[New/Used]],[Brand],"="&[@Brand],
[Year],"="&[@Year])<1),[@[Customer ID]],""),"")

The Idea is to have a clean Funnel through a Distinct Count of Customer ID.

Funnel Axis    Values    (All Values are Distinct Count)

Leads             45       Total Customers (The Sum of Lost Sale + Sales + In Progress) should always be = or > than Leads)
Lost.Sale        21       CODE Above Required in DAX to search If any of the rows is sold or open to exclude from Lost Sale.
Sold T            15       Distinct Count of Customer ID whom Purchased. (Columns contains Customer ID if Purchased)
Open              14       Distinct Count of Customer ID whom has Open Cases. (Columns contains Customer ID if Case still Open)

PHASE 2

The Above Code was my excel solution to my problem however, as you can see my filters in this case are now hard coded to scan on the whole table appying only those filters that were hard-coded.

What if in future I need to filter the same for a specific salesman ? I would then need to go and re-code this ? Then I need it for only Quarter 2 ? Again I would need to re-code this ?

I'm looking for a more dynamic solution where it would apply the filters within the code as I go on filtering on the dashboard.

Note: I think this is my first post and I hope its in the correct forum. 

[technolog · Accepted solution]

The problem you're trying to solve is that you want to create a DAX measure/column that respects the filters applied in Power BI and checks dynamically if specific columns are filtered before applying those filters in your logic.

To address your issue in a clear and systematic manner, I'll break it down into two main steps:

DAX Conversion: Convert your Excel formula into a DAX formula.
Dynamic Filtering: Implement dynamic filtering based on whether specific columns are being filtered or not.
Let's tackle the issues one by one:

1. DAX Conversion
The formula you have seems already converted into DAX:

Lost Sales =
IF(
OR(Leads[Lead Status]="7-LOST SALE",Leads[Lead Status]="X"),
IF(
AND(
COUNTROWS(FILTER(Leads,[Sold T]=EARLIER(Leads[Customer ID]) && [Year]=EARLIER(Leads[Year]) && [New/Used]=EARLIER(Leads[New/Used]) && [Brand]=EARLIER(Leads[Brand]) ))<1,
COUNTROWS(FILTER(Leads,[Open]=EARLIER(Leads[Customer ID]) && [Year]=EARLIER(Leads[Year]) && [New/Used]=EARLIER(Leads[New/Used]) && [Brand]=EARLIER(Leads[Brand])))<1
),
[Customer ID],
0
),
0
)
2. Dynamic Filtering
For the dynamic filtering part, you can make use of the ISFILTERED function to check if a particular column is being filtered, and then apply that filter conditionally.

Here's a basic structure:

If (ISFILTERED(Leads[Year]), FILTER(Leads, Leads[Year] = EARLIER(Leads[Year])), Leads)
Incorporate this structure into the previous DAX formula:

Lost Sales =
IF(
OR(Leads[Lead Status]="7-LOST SALE",Leads[Lead Status]="X"),
IF(
AND(
COUNTROWS(
FILTER(
Leads,
[Sold T] = EARLIER(Leads[Customer ID])
&& IF(ISFILTERED(Leads[Year]), Leads[Year] = EARLIER(Leads[Year]), TRUE)
&& IF(ISFILTERED(Leads[New/Used]), Leads[New/Used] = EARLIER(Leads[New/Used]), TRUE)
&& IF(ISFILTERED(Leads[Brand]), Leads[Brand] = EARLIER(Leads[Brand]), TRUE)
)
) < 1,
COUNTROWS(
FILTER(
Leads,
[Open] = EARLIER(Leads[Customer ID])
&& IF(ISFILTERED(Leads[Year]), Leads[Year] = EARLIER(Leads[Year]), TRUE)
&& IF(ISFILTERED(Leads[New/Used]), Leads[New/Used] = EARLIER(Leads[New/Used]), TRUE)
&& IF(ISFILTERED(Leads[Brand]), Leads[Brand] = EARLIER(Leads[Brand]), TRUE)
)
) < 1
),
[Customer ID],
0
),
0
)
The ISFILTERED checks will determine whether a particular column has a filter applied. If it's filtered, then that specific condition in your formula is used, otherwise, it defaults to true (which in effect ignores that filter condition).

This DAX formula should now respect any filter applied in your Power BI report. As you apply or remove filters, this formula will dynamically adjust its calculation.

Remember to adjust table and column references accordingly and always test thoroughly to ensure the desired results.

[AlAlawiAlawi]

Lost Sales = IF(OR(Leads[Lead Status]="7-LOST SALE",Leads[Lead Status]="X"),IF(AND(COUNTROWS(FILTER(Leads,[Sold T]=EARLIER(Leads[Customer ID]) && [Year]=EARLIER(Leads[Year]) && [New/Used]=EARLIER(Leads[New/Used]) && [Brand]=EARLIER(Leads[Brand]) ))<1,COUNTROWS(FILTER(Leads,[Open]=EARLIER(Leads[Customer ID]) && [Year]=EARLIER(Leads[Year]) && [New/Used]=EARLIER(Leads[New/Used]) && [Brand]=EARLIER(Leads[Brand])))<1),[Customer ID],0),0)

Multiple Filters where achieved using && between each filter logic.

Phase 2 Start .... How do we make the logic effective only when the Column had been filtered. (ISFILTERED) but how is the question now! ?

[AlAlawiAlawi]

Basically I am Filtering out the [Sold T] & [Year] & [New/Used] & [Brand] Columns while checking if the [Customer ID] fits the criteria.

Then again Filtering Out the [Open] & [Year] & [New/Used] & [Brand] Columns while checking if the [Customer ID] fits the criteria.

I want to use ISFILTERED as a function to check that these filters apply only when the data on each column had been applied.

So if the [Year] is filtered then only the year filter would apply, else not and would skip checking it on the Formula.

Then if the [Year} & [Brand] are filtered then both Filters would apply in the formula and so on ...

[AlAlawiAlawi]

Any idea where i could get support to this job ?