Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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:
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:
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 if all the tables in your data model are related, you can apply those filters.
Below is a link to my project:
https://github.com/anthonynguyen3/Power-BI/blob/master/WireEDM%20-%20MW2%20BI.excel.pbix
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |