Forum Discussion

MichaelSt's avatar
MichaelSt
Frequent Visitor
5 years ago
Solved

Remove Empty Colums

Hi community,

I have an tabe that has the following format:
Based on a slider on a dropdown on Formname, The records of the table are filtered. 

Formname | SubmissionDate | FirstName | Lastname | Email | City | Address

Sometimes for a specific Formname, the Email, city, Adress or other n colums are empty. 
The customer really wants to get the table filtered and remove the colums that are empty from the report and from the export of the data. I dont know how the handle this problem. 
I thought about a measure that filters the table and removes the blank columns, but I'm not skilled enough to do that. 
Do I even have chances here to achive this with dax?
Thanks in advance!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi MichaelSt ,

    You could try the following steps：

    Step1: create a measure like below:

    Measure = 
IF (
    MAX ( 'Table'[SubmissionDate ] ) = BLANK ()
        || MAX ( 'Table'[FirstName] ) = BLANK ()
        || MAX ( 'Table'[FirstName] ) = BLANK ()
        || MAX ( 'Table'[Lastname] ) = BLANK ()
        || MAX ( 'Table'[Email] ) = BLANK ()
        || MAX ( 'Table'[City] ) = BLANK ()
        || MAX ( 'Table'[Address] ) = BLANK (),
    BLANK (),
    1
)

    Step 2: configure the filters:

     

    base data:

     

    and final：

     

     

    Wish it is helpful for you!

    Best Regards

    Lucien

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI MichaelSt 

    Create a measure as below and use it in the Filter section of the report and set it always filter to 1.

    -Filter = IF(ISBLANK(Formname) || ISBLANK(SubmissionDate) || ISBLANK(FirstName) || ISBLANK(Lastname) || ISBLANK(Email) || ISBLANK(City) || ISBLANK(Address), 0, 1)

    • MichaelSt's avatar
      MichaelSt
      Frequent Visitor
      5 years ago

      Hi, thanks for your answer first of all.
      I guess thats not quite what I'm looking for. Thats a DAX for a calculated column, not for a Measure, right? At least I can only use it there..

      Either way doesn't the filter remove columns. It would only filter the rows right?
      I need to remove the columns if they are empty from the Visual

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi MichaelSt ,

    You could try the following steps：

    Step1: create a measure like below:

    Measure = 
IF (
    MAX ( 'Table'[SubmissionDate ] ) = BLANK ()
        || MAX ( 'Table'[FirstName] ) = BLANK ()
        || MAX ( 'Table'[FirstName] ) = BLANK ()
        || MAX ( 'Table'[Lastname] ) = BLANK ()
        || MAX ( 'Table'[Email] ) = BLANK ()
        || MAX ( 'Table'[City] ) = BLANK ()
        || MAX ( 'Table'[Address] ) = BLANK (),
    BLANK (),
    1
)

    Step 2: configure the filters:

     

    base data:

     

    and final：

     

     

    Wish it is helpful for you!

    Best Regards

    Lucien