Display distinct text after user has selected a set of filters

[olimilo]

I'm currently using the following Measure to display a text with a Long Text Viewer when the user selects a Country from the slicer:

Notes = 
IF(
    HASONEFILTER(Data[Country]),
    CONCATENATEX(Data, Data[Comments], "\n\n"),
    "Please select only one Country from the list."
)

However, I've now separated [Comments] from the Data dataset into another dataset called Comment. I'm not sure how the relationships quite work but I checked the Manage Relationships dialog box and a 1:M relationship is active between the Data and Comment datasets.

This is how both datasets look like:

> Data dataset
Month | Year | Region | Prod % | ...
Jan   | 2016 | NAM    | 80%    | ...
Jan   | 2016 | LAM    | 40     | ...
Jan   | 2016 | EUR    | 60%    | ...
Jan   | 2016 | AFR    | 70%    | ...
Jan   | 2016 | SEA    | 80%    | ...
Jan   | 2016 | GCN    | 70%    | ...

> Comment dataset
Month | Year | Region | Comments
Jan   | 2016 | NAM    | Jan NAM comment here
Jan   | 2016 | LAM    | Jan LAM comment here
Jan   | 2016 | EUR    | Jan EUR comment here
Jan   | 2016 | AFR    | Jan AFR comment here
Jan   | 2016 | SEA    | Jan SEA comment here
Jan   | 2016 | GCN    | Jan GCN comment here

Is it possible that when the user selects a [Month], [Year] and [Region] from the Data dataset, it would display the appropriate [Comments] from the Comment dataset? Also, I'm not sure on how Primary Keys work in Power BI (both datasets don't have any Primary Keys).

[dedelman_clng]

Filters follow the relationship, and since PowerBI was able to create the 1:M relationship between Data and Comments, any filter applied to Data should automatically be applied to Comment.

You will have to change your formula in CONCATENATEX to iterate of the Comments table, and probably have to use the RELATEDTABLE function.  Also, you show the detail for DATA and COMMENT, but there must be some index key joining them?

Ideally you would want

> Data dataset
Idx | Month | Year | Region | Prod % | ...
1 | Jan   | 2016 | NAM    | 80%    | ...
2 | Jan   | 2016 | LAM    | 40     | ...
3 | Jan   | 2016 | EUR    | 60%    | ...
4 | Jan   | 2016 | AFR    | 70%    | ...
5 | Jan   | 2016 | SEA    | 80%    | ...
6 | Jan   | 2016 | GCN    | 70%    | ...

and

> Comment dataset
DateIdx | Month | Year | Region | Comments
1 | Jan   | 2016 | NAM    | Jan NAM comment here
2 | Jan   | 2016 | LAM    | Jan LAM comment here
3 | Jan   | 2016 | EUR    | Jan EUR comment here
4 | Jan   | 2016 | AFR    | Jan AFR comment here
5 | Jan   | 2016 | SEA    | Jan SEA comment here
6 | Jan   | 2016 | GCN    | Jan GCN comment here

(in this case the month/year/region on Comment is superfluous, but should do no harm).  This setup would allow you to have multiple lines in Comment for the Month/Year/Region combo.

Hope this helps

David

[olimilo]

Hi David,

Thanks for the information! Unfortunately, both datasets do not have any keys/indices. Coming from SQL, I understand the need for the keys; however, the data used in these datasets were manually created (ie: copy-pasted from several reports) and didn't come from a database. In reality, the data would look like this:

> Data dataset
Month | Year | Region | Country | Prod % | ...
Jan   | 2016 | NAM    | USA     | 80%    | ...
Jan   | 2016 | NAM    | Canada  | 70%    | ...
Jan   | 2016 | LAM    | Brazil  | 60%    | ...
Jan   | 2016 | LAM    | Mexico  | 40%    | ...
Jan   | 2016 | GCN    | China   | 30%    | ...
Jan   | 2016 | SEA    | Taiwan  | 20%    | ...

So, if I understand what you're trying to say, the datasets should have keys right? Like so:

> Data dataset

Key | CID | Month | Year | Region | Country | Prod % | ...

1   | 1   | Jan   | 2016 | NAM    | USA     | 80%    | ...
2   | 1   | Jan   | 2016 | NAM    | Canada  | 70%    | ...
3   | 2   | Jan   | 2016 | LAM    | Brazil  | 60%    | ...
4   | 2   | Jan   | 2016 | LAM    | Mexico  | 40%    | ...
5   | 3   | Jan   | 2016 | GCN    | China   | 30%    | ...
6   | 4   | Jan   | 2016 | SEA    | Taiwan  | 20%    | ...

> Comment dataset
FK  | Month | Year | Region | Comment
1   | Jan   | 2016 | NAM    | Jan 2016 NAM Comment here
2   | Jan   | 2016 | LAM    | Jan 2016 LAM Comment here
3   | Jan   | 2016 | GCN    | Jan 2016 GCN Comment here
4   | Jan   | 2016 | SEA    | Jan 2016 SEA Comment here

EDIT:

So I added indices to both datasets and it now mirrors the ones I presented above. I'm able to achieve my objective through the code below, but is it possible to have it so that the code triggers only when the user has selected a [Region], [Year] and [Month]?

COLComment = 
IF(
	ISBLANK(RELATED(Comments[Comment])),
	"No additonal information available.",
	RELATED(Comments[Comment])
)

I tried the one below, and I keep on getting a (Blank) value even though I got TRUE as a result for the IF condition.

COLComment = 
IF(
	HASONEFILTER(Data[Year]) && HASONEFILTER(Data[Month]) && HASONEFILTER(Data[Region]),
	RELATED(Comments[Comment]),
	"No additonal information available."
)

[dedelman_clng]

Hi @olimilo - I'm curious as to how PowerBI relating the two sets together in the model if there was not a unique key on one of them?  That was the reason for my suggestion of indices - you can't join tables in PowerBI using multiple columns the same way you can in SQL.

[v-ljerr-msft]

Hi @olimilo,

According to your description above, it seems that you're using the formula above to add a new column to your "Data" table, right?

Not like a measure, calculate column won't respond to user selection on the report. So you should create a measure instead, then show it on the report in this scenario.

The formula below to create the measure is for your reference.

COLComment =
IF (
    HASONEFILTER ( Data[Year] ) && HASONEFILTER ( Data[Month] )
        && HASONEFILTER ( Data[Region] ),
    VALUES ( Comments[Comment] ),
    "No additonal information available."
)

Or

COLComment =
IF (
    HASONEVALUE ( Comments[Comment] ),
    VALUES ( Comments[Comment] ),
    "No additonal information available."
)

Regards

[olimilo]

@v-ljerr-msft

Hi! Thank you for the reference code. I tried both of them out (as a measure) and got this error:

COLComment =
IF (
    HASONEFILTER ( Data[Year] ) && HASONEFILTER ( Data[Month] )
        && HASONEFILTER ( Data[Region] ),
    VALUES ( Comments[Comment] ),
    "No additonal information available."
)

Calculation error in measure COLComment: a table of multiple values was supplied where a single value was expected.

And as for the code below, I only kept getting the ELSE statement, even if I slice the data in such a way that guarantees that there will be an output for the Comment.

COLComment =
IF (
    HASONEVALUE ( Comments[Comment] ),
    VALUES ( Comments[Comment] ),
    "No additonal information available."
)

@dedelman_clng

Right, so as I was saying, the data didn't really come from a database that had primary/foreign keys. These were all manually collated from several reports and we were only using Power BI to visualize the data. I've now seen how to better model the data by applying the same concepts used in databases. So I created keys for both tables, and added FKs to the Data dataset that relates to the PK of the Comments dataset.

[dedelman_clng]

@olimilo I haven't tested the code, but it sounds like you have multiple comments being pulled back (the sample data you provide shows only 1 comment).

Try changing VALUES to CONCATENATE (using the first measure code) and see if that works.

[olimilo]

@dedelman_clng

It really isn't all too different from the dataset I presented earlier. I just removed the Month and Year columns from the Comment dataset so it now looks like this:

Key	Region	Comment
1	NAM	Jan 2016 Comment here
2	NAM	Feb 2016 Comment 2 here
3	NAM	Mar 2016 Comment 3 here
4	NAM	Apr 2016 Comment 4 here
5	NAM	May 2016 Comment 5 here
6	NAM	Jun 2016 Comment 6 here

I tried CONCATENATE as you suggested but I'm also getting an error: A single value for column 'Comment' in table 'Comments' cannot be determined.

I don't understand, why is it always resulting into multiple rows, given that I only selected one set of Month, Year and Region (that would isolate the dataset to just one Data[CFK]) right? Also, the two tables are now related by CFK (Data) to Key (Comment) in a M:1 relationship.

[dedelman_clng]

You may need to share out your PBIX (with appropriate data anonymized) to troubleshoot this further.  I put your mock data into PowerBI and used the measure formula and got no errors.  By altering it slightly I was able to get the "No additional information" message suppressed correctly.

Comment

Data

COLComment = 
IF (
    HASONEVALUE ( TabData[Year] ) && HASONEVALUE ( TabData[Month] )
        && HASONEVALUE ( TabData[Region] ),
    VALUES ( TabComment[Comment] ),
    "No additonal information available."
)

Note that in a table visual there are implicity filters on each row, so selecting or de-selecting anything in the slicer just limits the number of rows.