Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Can someone check this DAX formula please?

So my ultimate goal is: 

- Filter for only regions = Midwest 2

- Filter for only contract type = Extended Warranty + Parts only Mfg. Warranty + Remote Services 

 

Current Problem: the output will return the filter for the contract types but will not honor the region filter. Any idea on why this region filter will not work? 

 

ContractSoldPath =
CONCATENATEX(
UNION(
FILTER(Contracts_sold, Contracts_sold[Region] = "Midwest 2"),
FILTER(Contracts_sold, Contracts_sold[contract_type] = "Extended Warranty"),
FILTER(Contracts_sold, Contracts_sold[contract_type] = "Parts only Mfg. Warranty"),
FILTER(Contracts_sold, Contracts_sold[contract_type] = "Remote Services")
),
'Contracts_sold'[machine], "|")

 

ALSO, I tried to insert 2 additional filters but there is always an error...pretty sure I butchered the DAX syntax but was hoping that someone would be able to shed some advice on how to correctly apply the DISTINCT + ALLEXCEPT function into this syntax: 

- FILTER(Contracts_sold, DISTINCT(Contracts_sold[contract_id])) --> the goal is to only get distinct return for this field but not working

- ALLEXCEPT(Filter(Contracts_sold, Contracts_sold[contract_type] = "Prev. Maintenance")) --> the goal is get all of the contract types EXCEPT prev. maintenance

 

ContractSoldPath =
CONCATENATEX(
UNION(
FILTER(Contracts_sold, DISTINCT(Contracts_sold[cconth_id])),
FILTER(Contracts_sold, Contracts_sold[Region] = "Midwest 2"),
ALLEXCEPT(Contracts_sold,Contracts_sold[contract_type] = "Prev. Maintenance")
),
'Contracts_sold'[machine], "|")

 

Any guidance from the community will be greatly appreciated! 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello @Anonymous i think this what you need

 

TEST =
CALCULATE (
    CONCATENATEX ( MW2_WEDM_machines; MW2_WEDM_machines[Machine_ID]; " | " );
    FILTER ( Contracts_sold; Contracts_sold[Region] = "Midwest 2" );
    FILTER (
        Contracts_sold;
        Contracts_sold[contract_type]
            IN { "Extended Warranty""Parts only Mfg. Warranty""Remote Services" }
    )
)

 

I downloaded the file, this how it looks with this measure:

 

Captura.PNG

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hello @Anonymous i think this what you need

 

TEST =
CALCULATE (
    CONCATENATEX ( MW2_WEDM_machines; MW2_WEDM_machines[Machine_ID]; " | " );
    FILTER ( Contracts_sold; Contracts_sold[Region] = "Midwest 2" );
    FILTER (
        Contracts_sold;
        Contracts_sold[contract_type]
            IN { "Extended Warranty""Parts only Mfg. Warranty""Remote Services" }
    )
)

 

I downloaded the file, this how it looks with this measure:

 

Captura.PNG

 

 

Anonymous
Not applicable

Stunning! Simply Stunning! Thank you so much! 

 

Question for you, this DAX formula applies only to filter the Contracts_sold table. How would you apply a simular filter across the FS History table + Pending PM tables?

 

Ultimately, all of the customers are listed in the master MW2_WEDM data table and I would like to filter down from that table because all of the other tables (Contracts_sold, FS History, and Pending PM) are derived from the MW2_WEDM data table. 

Anonymous
Not applicable

@Anonymous if all the tables in your data model are related, you can apply those filters.

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.