Filtering Rows with no value in multiple columns

[third_hicana]

Hi. May I ask your help please how to create the DAX of my scenario.

In the table visual, I want only to show Project T and Y because they have no values from Q1 to Entire Duration columns. So If I use the Department Manager slicer and select DM1 , I want to show only Project T and Y.

Columns 'ID' and 'BOP Title' is in another table, then 'Department column up to 'Entire Duration' is also another table in my data model. The BOP ID column was used to create relationship for the two tables. 

Thank you for your help

Thanks,

Third

[Anonymous]

Hi @third_hicana 

You can filter out the relevant blank columns in the filter before using the slicer.

Best Regards,

Jayleny

[123abc]

To achieve the desired filtering in your scenario, you can create a calculated column or measure in your data model using DAX. If you want to filter rows where there are no values from Q1 to Entire Duration columns for each project, you can create a measure that checks if all these columns are blank for a particular project. Here's how you can create such a measure:

ShowProject =
IF (
ISBLANK ( 'YourTable'[Q1] )
&& ISBLANK ( 'YourTable'[Q2] )
&& ISBLANK ( 'YourTable'[Q3] )
&& ISBLANK ( 'YourTable'[Q4] )
&& ISBLANK ( 'YourTable'[Entire Duration] ),
"Show",
BLANK ()
)

This measure evaluates if all the specified columns (Q1 to Entire Duration) are blank for each row. If they are all blank, it returns "Show"; otherwise, it returns blank.

After creating this measure, you can use it as a filter in your table visual. Drag the ShowProject measure into the visual level filters of your table visual, and filter for rows where the measure is equal to "Show". This will display only those projects where the conditions are met (i.e., where there are no values from Q1 to Entire Duration).

Additionally, if you want to further filter the projects based on the Department Manager slicer selection (e.g., DM1), you can adjust the measure accordingly by incorporating the logic related to the Department Manager slicer selection. You would need to consider the relationship between your tables and how the slicer interacts with your data model.

Remember to replace 'YourTable' with the actual name of your table, and adjust column names as per your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.