Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Find articles, guides, information and community news

Most Recent
ibarrau
Super User
Super User

I know this practice for a long time, and I haven't used a lot until a month ago when a user helped me understand the importance of explaining the blank for their experience. Usually when I use to had blank visual, I didn't do anything about it because I was assuming it was obvious. If you filter data and the visual stays blank, it's because you don't have something the see there.

It sounds obvious but many people not related with Bi tools assume there is a failure or issue with the development, and they don't think about a data issue. That's one of many reasons why we need to know this practice of showing a message explaining different situations when that happen. Let's help the users experience and issues perceptions. At the end of the post, we might have even more ideas to develop.

Read more...

v-henryk-mstf
Community Support
Community Support

Define a temporary function or multiple variables in a single column

Read more...

v-jingzhang
Community Support
Community Support

Comparison of the text string with multiple elements

Read more...

Vijay_A_Verma
Super User
Super User

This article gives DAX formulas to calculate Last Mon, Tue, Wed, Thu, Fri, Sat and Sun of the Month.

Read more...

Vijay_A_Verma
Super User
Super User

This article offers formulas to simulate STARTOFWEEK and ENDOFWEEK functions like Date.StartOfWeek and Date.EndOfWeek functions of Power Query language M

Read more...

v-henryk-mstf
Community Support
Community Support

Group your data according to dynamic measure results

Read more...

v-rzhou-msft
Community Support
Community Support

This is an article about how to calculate about matrix subtotal.

Read more...

v-henryk-mstf
Community Support
Community Support

Understand the Filter Context and How to Control it

Read more...

v-yiruan-msft
Community Support
Community Support

Tips for handling common scenarios of employee attendance sheets

Read more...

v-yiruan-msft
Community Support
Community Support

Scenario

Sometimes, we need to pick some data as a sample to measure the overall situation randomly. For example, a factory produces multiple products, and each product needs to pick out a certain amount of samples to measure the overall pass rate. Moreover, in the selection process, it is also necessary to exclude the factor of human choice. So, how to use Power BI to realize this requirement?

Next, let us take a concrete look at different scenarios and needs.

Note:

In this blog, we only discuss how to sample, not the subsequent process of judging whether it is qualified or not.

Here is a sample table: Table1. And you can find others in the attached .xlsx file. 

Product

Product ID

Production Date

Metrics

Product A

1001

2022/3/12

1.37

Product A

1002

2022/3/12

1.69

Product A

1003

2022/3/13

2.23

Product A

1004

2022/3/14

1.98

Product B

2001

2022/3/12

2.01

Product B

2002

2022/3/13

3.04

Product B

2003

2022/3/13

2.65

Product B

2004

2022/3/14

1.75

Product B

2005

2022/3/14

2.23

Product C

3001

2022/3/13

4.11

Product C

3002

2022/3/13

3.51

Product C

3003

2022/3/13

3.66

Product C

3004

2022/3/14

2.35

Product C

3005

2022/3/15

4.01

Product C

3006

2022/3/16

2.98

 

Part1: Randomly select a few rows of records from the entire table

In Power BI, there is a DAX function named SAMPLE. It will return a sample of N rows from the specified table. And if OrderBy arguments are pro...

Here are examples:

EX1: Return a calculated table in which 3 samples are randomly selected from Table1.

Table1_Sample_1 =

SAMPLE (

    3,                  /* The number of rows to return. */

     Table1,         /* Any DAX expression that returns a table of data from where to extract the 'n' sample rows. */

     1                  /* Since the minimum argument count for SAMPLE function is 3, put any scalar DAX expression. You can also specify a column. */

     )

EX2: Return a calculated table in which 3 samples are randomly selected from Table1 and sorted by one specific column.

Table1_Sample_2 =

SAMPLE (

    3,

     Table1,

     [Metrics]         /* Sort by [Value] column. */

     )

In addition, from my limited testing, I find that SAMPLE function will not update the result returned after refresh if data has not been updated. Here is the test result:

Then how do we get different results after each refresh?

Let’s consider using RAND function. It works the same as in Excel, returning a random number greater than or equal to 0 a...

There are two methods.

a. Combined with SUMMARIZE, TOPN and ADDCOLUMNS functions to create a calculated table.

Table1_RAND =

SUMMARIZE (

    TOPN ( 3, ADDCOLUMNS ( Table1, "Rand", RAND () ), [Rand] ),

    [Product],

    [Product ID],

    [Production Date],

    [Metrics]

)

b. Create a calculated column in the original table and just return the filtered values in Table visual.

Note: To avoid affecting the test results above, we copy the Table1 below, generate Table2, and then create a new computed column in Table2.

Rand = RAND()

We can see that after each refresh, the returned rows are different.

 

Now, we know how to get sample rows from one table. Then, how do we get the same number of rows for each category? Let’s go to the next part.

 

Part2: Randomly select records with the same number of rows by category from the entire table

a. Based on my test, class-by-class sampling cannot be achieved by SAMPLE function in this scenario. For example:

Table1_Sample_3 =

VAR ProductNum_ =

    DISTINCTCOUNT ( Table1[Product] )

RETURN

    SAMPLE ( ProductNum_ * 2, Table1, [Product] )

b. Then we use RANK function to create measures based on the Rand column in Part1 and then use “Filters on this visual” feature.Rank_Table2 =

RANKX (

    ALLEXCEPT ( Table2, Table2[Category] ),

    CALCULATE ( SUM ( Table2[Rand] ) ),

    ,

    DESC,

    DENSE

)

So far, we have implemented sampling by category. Then, if some categories have fewer records and some categories have more records. Then how do we sample it? Let’s go to Part3.

 

Part3: Randomly select records with different rows by category from the entire table

 It is, in fact, very simple that we only need to combine the SWITCH function and add a judgment condition and then use “Filters on this visual” feature. For exam...

RankFilter_Table2 =
VAR Count_ =
    CALCULATE ( COUNTROWS ( Table2 ), ALLEXCEPT ( Table2, Table2[Product] )
VAR Threshold_ =
    SWITCH ( TRUE (), Count_ >= 8, 4, Count_ >= 6, 3, Count_ >= 4, 2, )
RETURN
    IF ( [Rank_Table2] <= Threshold_, )

 

If you want to get more details, please check the attached .pbix file.

Hope this blog can help you.

 

 

Author: Icey Zhang

Reviewer: Kerry Wang & Ula Huang

yingyinr_8-1647408887666.gif

yingyinr_7-1647408793068.gif

yingyinr_4-1647408701580.png

yingyinr_3-1647408626593.gif

yingyinr_2-1647408363640.gif

yingyinr_3-1647408085579.gif

yingyinr_1-1647407908935.png

yingyinr_0-1647407908930.png

Read more...

ibarrau
Super User
Super User

I have been giving support for DAX in different places. Something that is becoming more frequent are long DAX functions repeating code (they might be format, but long). Usually, these problems arrive when there is a performance issue that results in a long measure.

In this article I’m going to explain with my words the importance of using variables in our code. We should always consider using variables because they have several benefits. Some people might think they only help to make the code feel cleaner, but I can assure you it will help on maintainability too.

Read more...

V-lianl-msft
Community Support
Community Support

In this article, we will talk about how to look up values in either original table or another table. Now we will show you some examples to help you better understand it.

Read more...

V-lianl-msft
Community Support
Community Support

The Power BI DAX functions enable you to process data from multiple tables. Some users want to obtain some data from a filtered table, then use these data to find related items in another table, and finally perform a series of operations on them.
In this article, I will mainly introduce 2 parts. The first part introduces several ways to get related items from other tables. And the second part realizes the ranking of different tables based on the principles of scenario 1.

Read more...

V-lianl-msft
Community Support
Community Support

Some users want to filter and summarize based on column names, however, some difficulties arise due to the fact that dax statements basically operate on the data in the specified column.

For example, there is a report card with only names and test takers' scores for each subject, the school wants to have a report that can be filtered using a slicer for each subject's score, how can this be achieved?

Read more...

V-lianl-msft
Community Support
Community Support

When exploring and analyzing data, we may need to add colored icons to help us visually identify patterns and trends. Currently, there are two main ways to add icons.  
1. Icon sets for conditional formatting 
a) built-in icon sets 
b) custom icon sets (json file of theme) 
2. Conditional fields 
a) SVG xml, Img URL, Built-in icon name 
b) Html-code of Unicode characters 

Read more...

Helpful resources

Join Blog
Interested in blogging for the community? Let us know.