Get the values to the corresponding lines in a matrix

[Quenjo]

Hi community,

I created a Matrix that has three lines :

+Company

   +Server

      +Database

I want to do some calculations based on the specifications of the servers and the databases but not the company.

Let me take an example :

For the company line in the matrix I only want to have the Company Name.

Then, when I expand the company I have all the servers related to the concerned company. These server have a certain number of CPUs, of disk space, in brief, of specificities.

I would have only all the specificities "deployed" only and only when I expand a company, not always.

Let me show some screenshots :

As you can see here, I have the company name :

And the sum of all the specificities of all the servers belonging to this company. I do not want this "general" view.

How is it possible to have all the specificites deployed only for the concerned servers / concerned databases ?

Don't hesitate if it is not clear enough and you need more explanations !

Thanks for your help.

Best regards,

Jonathan

[tamerj1]

Hi @Quenjo 

I don't know about other methods that do not include DAX. 

If you are summarizing columns in the visual then you need to replace them with measures. Example measure would be 

Measure =
IF ( NOT ISINSCOPE ( TableName[Company Name], SUM ( TableName[Comulmn1] ) )

[Quenjo]

Hi @tamerj1,

Thanks for the reply. However, this will only give the SUM of the disk_space column and this is not what I am expecting.

Let me explain it better :

Here, I just want to have the 830 displayed in the same line as the server, and the "microsoft_sql_server" displayed in the same line as the database. Nothing should be displayed under the company.

If everything is always displayed, I will have something like this in the PowerBi "Homepage" :

The bofuscated field is the company name, the 156'260 represents the disk_space and the mssql server represents the database type. You understand that it makes no sense to just take the first type of database related to a company. I just want this information to be displayed under the column related to each database.

Hope you understand my issue. How can I solve this ?

Many thanks,

Jonathan

[tamerj1]

@Quenjo 
Would you please share the code that you've tried?

[Quenjo]

@tamerj1 ,

Espace disque = IF ( NOT ISINSCOPE ( company[Company Name]), SUM ( servers[disk_space] ) )

It just prints the total of the whole disk space of all servers without displaying the companies anymore.

Thanks for your help, @tamerj1 

[tamerj1]

@Quenjo 
Please try

Espace disque =
SWITCH (
    TRUE (),
    ISINSCOPE ( company[Database Name] ), SUM ( servers[disk_space] ),
    ISINSCOPE ( company[Server Name] ), SUM ( servers[disk_space] )
)

[Quenjo]

@tamerj1 

It does not work since I do not have the Database Name in the company table.

If I try to put something like this :

Espace disque =

SWITCH (

TRUE (),

ISINSCOPE ( databases[DB Name] ), SUM ( servers[disk_space] ),

ISINSCOPE ( servers[Server Name] ), SUM ( servers[disk_space] )

)

It does not work too... I really can not find a solution to this issue.

I tried to only put :

Espace disque = ISINSCOPE( servers[Server Name] )

And this works, since only the server line is set as True and the company and database line are set as False.

[tamerj1]

@Quenjo  😂

So you have multiple tables? Can you please give more details about your data model and relationships?

[Quenjo]

@tamerj1  Sure !

Hope thos helps. The Server table has a foreign key to the company Id (primary key) and the Database table has a foreign key to the server Id (primary key).

Hope this helps you out !

[tamerj1]

@Quenjo 
Try with HASONEVALUE

Espace disque =
SWITCH (
    TRUE (),
    HASONEVALUE ( databases[DB Name] ), SUM ( servers[disk_space] ),
    HASONEVALUE ( servers[Server Name] ), SUM ( servers[disk_space] )
)

[Quenjo]

@tamerj1 

It does not work.

Forget the databases table. The databases has not the disk_space, only the servers table contains disk_space. So, I want to display only the disk_space only for each server line when expanding a company.

The ISINSCOPE function seems to be right since the result of it is the following :

Where the "False" values represent the companies and the "True" values represent the servers. However, I do not understand how I can display the concerned column only and only for the "true" fields.

[tamerj1]

@Quenjo 

My apologies. I misunderstood your requirement. Please try

Espace disque =
SWITCH (
    TRUE (),
    ISINSCOPE ( databases[DB Name] ), BLANK (),
    ISINSCOPE ( servers[Server Name] ), SUM ( servers[disk_space] ),
    ISINSCOPE ( Company[Company Name] ), BLANK ()
)

[Quenjo]

@tamerj1 

No problem, you are trying to help and I really appreciate it !

When using your last formula I have the following :

Edit : While doing some tests, I found out that with your formula it works when expanding a company. In the sense that, in this formula we are saying :

if isinscope company Name then BLANK. So, the thing is, here nothing will be displayed. How can I say that if company name isinscope it will just display the company name and not blank ? Hope you understand.

Edit 2 : I found an interesting thing :

It seems that everything that is below the company name is in scope of the company name. Because we are saying that everything that is in scope of the company name must be BLANK. Nothing will be displayed. Any idea to avoid this ?

Any idea ?

[tamerj1 · Accepted solution]

@Quenjo 
Yes this is true. ISINSCOPE means if filtered by. All the levels below or equal to "company" for example are filtered by company therefore, eveything returns "True". This is why the order of the SWITCH arguments is essential to get the correct results. Usually we start from the bottom level up to the top level. 

[tamerj1]

@Quenjo 
😂🤣😂🤣

Yes it is confusing specially without having the data in front t of your eyes. I hope this will finally work

Espace disque =
SWITCH (
    TRUE (),
    HASONEVALUE ( databases[DB Name] ), BLANK (),
    HASONEVALUE ( servers[Server Name] ), SUM ( servers[disk_space] ),
    HASONEVALUE ( Company[Company Name] ), BLANK ()
)

[Quenjo]

@tamerj1 ,

Can you look at my Edit 2 ? It should really help us to find the solution to the problem.

[tamerj1]

@Quenjo 
Like this?

Espace disque =
SWITCH (
    TRUE (),
    HASONEVALUE ( databases[DB Name] ), BLANK (),
    HASONEVALUE ( servers[Server Name] ), SUM ( servers[disk_space] ),
    HASONEVALUE ( Company[Company Name] ), SELECTEDVALUE ( Company[Company Name] )
)