Working with Play Axis Visual with blank values for the filter

[Anonymous]

Hi, 

I have a Play Axis Visual that runs the ID to filter my report, that ID number goes from 1 to 10 and I have 4 tables with the ID column, but not all have 10 ID items.

For example, my table 1:

ID	Message
1	Message 1
2	Message 2
3	Message 3
4	Message 4
5	Message 5

My table 2:

ID	Product
1	Product 1
2	Product 2
3	Product 3
4	Product 4
5	Product 5
6	Product 6
7	Product 7
8	Product 8
9	Product 9
10	Product 10

When my play axis is running at ID 6, my card in table 1 is blank and I needed to create some way to bring a random value (considering MIN (table1 [ID]) and MAX (table1 [ID])) when the value field is equal to blank.

Could someone help me on how to solve this problem?

Another question I have is whether there is a way to use 2 play axis in the same page? I tried to do it, but one play axis ends up interfering with the other.

Thanks a lot!

[v-eqin-msft · Accepted solution]

Hello @vmzandonadi ,

In my opinion, you want to use the visual card to display the selected ID in visual Play Axis, when the ID does not exist in Table1, then show randon ID, right?

You can use the following formula:

1. Add a new table with all ID, and then create a column:

Column =
VAR _v =
    LOOKUPVALUE ( Table1[ID], [ID], 'ForSlicer'[ID] )
RETURN
    IF (
        _v = BLANK (),
        RANDBETWEEN ( MIN ( 'Table1'[ID] ), MAX ( 'Table1'[ID] ) ),
        _v
    )

2. Create a measure:

Measure 2 =
IF (
    MAX ( ForSlicer[ID] ) IN VALUES ( 'Table1'[ID] ),
    CALCULATE (
        MAX ( 'Table1'[ID] ),
        FILTER ( 'Table1', 'Table1'[ID] = MAX ( ForSlicer[ID] ) )
    ),
    RANDBETWEEN ( MIN ( 'Table1'[ID] ), MAX ( 'Table1'[ID] ) )
)

My final output looks like this:

Here's the pbix file.

Have I answered your question? Please mark my answer as a solution. Thanks a lot.
If not, load some insensitive data samples and expected output.

Best regards
Eyelyn Qin

[Anonymous]

Hi @Eyelyn9 thank you so much, this is exactly what I need.

Thanks for sending the pbix file, this made it easier to understand 🙂

[Anonymous]

@amitchandak thank you for the answer, but it's not exactly what I need. I need for the other charts that I have in my visual to be filtered by ID 6, only for the table 1, I need to randomize (or not maybe some other way) any value between MIN e MAX ID that I have in this table.

I don't know if I managed to be clear, maybe create a new column in my table, with the ID values from 1 to 10, and for those that are blank I bring some random data that I have filled in the table.

I tried many ways to do it that didn't work, maybe I'm not thinking the right way.

[amitchandak]

@Anonymous , You can use randbetween

if(isblank([measure]), randbetween(MIN (table1 [ID]) , MAX (table1 [ID]))

refer

https://www.youtube.com/watch?v=LFAT7AEDk5Q