How to make this possible (showing blank record)?

[JustinDoh1]

I have upload my Powerbi copy here.

On the visual, I have three people who are counted in "Census", but only one person (John Taylor) has Immunizations.

So, when I click the "Taylor, John", I am able to see the next hierarchy records - details of Immunizations:

But, the issue is, other two people have disappeared from the visual.

I am trying to see is if I click the + , I would like to see blank record shown but keep the person's name.

Is it possible?

[Anonymous]

Hi all,thanks for the quick reply, I'll add more.

Hi @JustinDoh1 ,

Regarding your question, simply change your measure value format to "Text" and concatenate a string after the blank value.

DISTINCTCOUNT(Immunizations[ClientID]) &""

Best Regards,
Wenbin Zhou

[JustinDoh1]

@Anonymous 

Thank you for your help. Unfortunately, this is not what I am looking for.

I am still wondering if this is possible (with one blank line underneath "Doe, Jane" and "Dean, James"):

[JustinDoh1]

@Ahmedx Thank you for your help. When I clicked the + for "Doe, Jane" and "Dean, James", other immunizations data show up that belong to "Taylor, John", and that is not good. I don't think this would work. Thanks.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[JustinDoh1]

@Ashish_Mathur 

Thank you for your help. I have shared the link on the original post.

But here is the same link.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[JustinDoh1]

@Ashish_Mathur  Thank you for your help.

Actually, I want to show blank when I click the + for "Doe, Jane" and "Dean, James" something like below, but I would not take that whole space.

[Ashish_Mathur]

You are welcome.  Someone who really understand your requirement will help.

[shafiz_p]

Hi @JustinDoh1  Right click on Rows in the Build Pane option and check Show Item with no data and you are done. Check image:

After expanding JohnTaylor:

Clicked all + buttons:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[JustinDoh1]

@shafiz_p  Thank you for your help.

I am going to see if this work with my manager, but I was expecting to see "blank row" when the + is clicked. Do you think it is possible?

[shafiz_p]

Use count of client ID as a measure. You will be able to see all blank rows.

[JustinDoh1]

@shafiz_p 

Can you please eleborate on this or possibly show on image or on the updated PBI file?

[shafiz_p]

Hi @JustinDoh1  Previously I mis understood what you need actually. You want a blank rows when expand fullname, rather repeating all ImmunizationDesc for client who has blank distinct count. Why you need such requirement I don't understand, where show Item with no data covers the full picture.

However, to achieve this, you need a bridge table. Check this out, if it works:
1. First Create a BridgeTable, with the crossjoin between clientID from Census Table and ImmunizationDesc from Immunizaiton Table. Try below code to create such table:

BridgeTable = 
CROSSJOIN(
    DISTINCT(Census[ClientID]),
    DISTINCT(Immunizations[ImmunizationDesc])
)

2. Create a DescriptionStatus column which evaluates which description is in the original data table and which are not. If, then return such description else blank. Try below code:

DescriptionStatus = 
IF(
    COUNTROWS(
        FILTER(
            Immunizations, 
            Immunizations[ClientID] = BridgeTable[ClientID] && 
            Immunizations[ImmunizationDesc] = BridgeTable[ImmunizationDesc]
        )
    ) > 0,
    BridgeTable[ImmunizationDesc],
    ""
)

3. Now create one to many relationship between census[ClientID] and BridgeTable[ClientID]. See image:

4. Update your Census Count measure with the below code:

Census Count = 
CALCULATE(
    DISTINCTCOUNT(Immunizations[ClientId]),
    TREATAS(
        VALUES(BridgeTable[ClientID]),
        Immunizations[ClientID]
    ),
    BridgeTable[DescriptionStatus] <> "No Desc"
)

Here, TreatAS creating a virtual relationship between the 2 tables through ClientID.

5. Now replace ImmunizationDesc column of Immunizations table from matrix row section and place DescriptionStatus column from BridgeTable in the row section and place newly updated measure in the value section and replace the previous one.

Here is the updated version of matrix output:

You can use any description in this blank if you want. For example, I want to notify user that no description. Just replace blank in the BridgeTable blank with "No Desc" , and you are done. Check out:

Hope this meets your requirement.
If , please accept it as a solution and a kudos!!

Best Regards, 
Shahariar Hafiz

[JustinDoh1]

@shafiz_p I am having difficulty replicating Part 5.

I have uploaded my PBI file with what you indicated here.

Can you please take a look and possibly send me/share the updated PBI file that works (with what you have indicated)? Thank you so much for your help.

I would like to have a "" instead of "No Desc".

[shafiz_p · Accepted solution]

update your Census Count measure "Census Count = DISTINCTCOUNT(Immunizations[ClientId])"

with the below code:

Census Count=
CALCULATE(
    DISTINCTCOUNT(Immunizations[ClientId]),
    TREATAS(
        VALUES(BridgeTable[ClientID]),
        Immunizations[ClientID]
    ),
    BridgeTable[DescriptionStatus] <> ""
)

See after updating measure, this is working exactly as you have requested. 

Please accept it as a solution, if this works and a kudos!!

[Ahmedx]

pl try this