Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How do I create a new table that only shows the actual module names tagged as N in column In use.
Conditions:
1. If sublevel III has no value, sublevel II value should be listed
2. If sublevel II has no value, sublevel I value be listed.
3. If sublevel I has no value, module value should be listed.
Category | Module | (Module) Sublevel I | (Module) Sublevel II | (Module) Sublevel III | In Use | |||||
SETTINGS | Application Configuration | Accounts Configuration | Y | |||||||
SETTINGS | Masters | Agency Master | Agency Local Charges | N | ||||||
SETTINGS | Masters | Documentation Masters | MCN Configuration | MCN Validation | Y | |||||
Commercial | Contribution | Cost | Masters | Cost Type | N | |||||
Commercial | Sales Visit Plan | N | ||||||||
Accounting | Agency Commission Statement | N | ||||||||
Accounting | Refund Payment | N | ||||||||
Operations | Terminal Departure Receipt | N | ||||||||
Operations | Reports | Cont'r Weight Discrepancy | N |
Desired Table :
Category | Module Name | In Use | ||
SETTINGS | Agency Local Charges | N | ||
Commercial | Cost Type | N | ||
Commercial | Sales Visit Plan | N | ||
Accounting | Agency Commission Statement | N | ||
Accounting | Refund Payment | N | ||
Operations | Terminal Departure Receipt | N | ||
Operations | Container Weight Discrepancy | N |
Solved! Go to Solution.
Hi @dnsia ,
You can create a new column in your current table with below code:-
Non_blank_module =
IF (
Module_data[(Module) Sublevel III] <> BLANK (),
Module_data[(Module) Sublevel III],
IF (
Module_data[(Module) Sublevel II] <> BLANK (),
Module_data[(Module) Sublevel II],
IF (
Module_data[(Module) Sublevel I] <> BLANK (),
Module_data[(Module) Sublevel I],
Module_data[Module]
)
)
)
Now you can use Category,Non_Blank_module,in user column from this table with IN-Use = N filter:-
or you can create a new table with code:-
Table 3 =
SUMMARIZE (
FILTER ( Module_data, Module_data[In Use] = "N" ),
Module_data[Category],
Module_data[Non_blank_module],
Module_data[In Use]
)
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @dnsia ,
You can create a new column in your current table with below code:-
Non_blank_module =
IF (
Module_data[(Module) Sublevel III] <> BLANK (),
Module_data[(Module) Sublevel III],
IF (
Module_data[(Module) Sublevel II] <> BLANK (),
Module_data[(Module) Sublevel II],
IF (
Module_data[(Module) Sublevel I] <> BLANK (),
Module_data[(Module) Sublevel I],
Module_data[Module]
)
)
)
Now you can use Category,Non_Blank_module,in user column from this table with IN-Use = N filter:-
or you can create a new table with code:-
Table 3 =
SUMMARIZE (
FILTER ( Module_data, Module_data[In Use] = "N" ),
Module_data[Category],
Module_data[Non_blank_module],
Module_data[In Use]
)
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
you can either transform the table in pq or use DAX to create a new table.
pls see the attachment below.
Proud to be a Super User!
@dnsia , Unpivot the module column in power and filter on N and save and try
https://radacad.com/pivot-and-unpivot-with-power-bi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |