CODEWITHSUNDEEP
powerbi
Peter
Peter

Reputation: 11

power BI diaplay one value

I am using Power BI to bring together data from several systems and display a dash board with data from all of the systems.

The dashboard has a couple of filters which are then used to display the data relating to one object across all systems.

When the dashboard is first loaded and none of the filter have been selected, the data cards display information from all rows in the table.

Is there a way to make a data card only display one row of data?

or

Be blank if there are more than one row of data?

Upvotes: 1

Views: 1374

Answers (1)

Leonard
Leonard

Reputation: 2578

There's no direct way to look at the number of rows in the visual, count them, and do something different if there's more than 1.

That said, there are a few things you can do.

HASONEFILTER

If you have a specific column in your table that, when selected, filters your results to a single row, then you can check if there's a filter on that column using HASONEFILTER. (If you have multiple alternative columns,any of which filter to a single row, that's ok too.)

You could then create a measure for each column that tests HASONEFILTER. If true, return the MAX of the column. (The reason for MAX is because measures always have to aggregate, but the MAX of a 1-row column will be the same as the value in that column.) If false, return either BLANK() or an empty string, depending on your preference.

E.g. 

ColumnAMeasure = IF(HASONEFILTER(Sheet1[Slicer Column]),MAX(Sheet1[COLUMN A]), "")
ColumnBMeasure = IF(HASONEFILTER(Sheet1[Slicer Column]),MAX(Sheet1[COLUMN B]), "")

where Sheet1 is the name of the table and "Slicer Column" is the name of the column being used as a slicer

HASONEVALUE

If you have multiple columns that could be used as filters in combination (meaning that having a filter applied on "Slicer Column" doesn't guarantee only 1 row in the table), then rather than testing HASONEFILTER, you can test HASONEVALUE.

ColumnAMeasure = IF(HASONEVALUE(Sheet1[COLUMN A]),MAX(Sheet1[COLUMN A]), "")
ColumnBMeasure = IF(HASONEVALUE(Sheet1[Column B]),MAX(Sheet1[COLUMN B]), "")

Notice that HASONEVALUE tests the current column you're trying to display, rather than a slicer column like HASONEFILTER.

One side-effect of HASONEVALUE is that, if you're filtered to 3 rows, but all 3 rows have the same value for column A, then column A will display that value. (Whereas with HASONEFILTER, column A would stay blank until you're filtered to one thing.)

Low Tech

Both answers above depend on a measure existing for every column you want to display, so that you can test whether to display a blank row or not. That could become a pain if you have dozens of columns.

A lower-tech alternative is to add in an additional row with blanks for each column and then sort your table so that that row always appears first. (And shorten your visual so only the top row is visible.) Technically the other rows would be underneath and there'd be a scrollbar, but at least the initial display would be blank rather than showing a random row.

Hopefully something here has helped. Other people might have better solutions too. More information:

HASONEFILTER documentation: https://msdn.microsoft.com/en-us/library/gg492135.aspx HASONEVALUE documentation: https://msdn.microsoft.com/en-us/library/gg492190.aspx

Upvotes: 1

Related Questions